<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" import="java.sql.DriverManager"%>
<%@page import="connection.DBConnection"%>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Sales Periodic Report</title>
</head>
<body>
<h2 align="center"> Sales Periodic Report</h2>
<form>
   <%  String exportToExcel = request.getParameter("exportToExcel"); 
   if (exportToExcel != null && exportToExcel.toString().equalsIgnoreCase("YES")) 
        {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "inline; filename=" + "SalesPeriodicReport.xls");
 
        }
    %>

<table border=1 align="center"> 
<tr>
	<th>Invoice Number</th>
	<th>Date</th>
	<th>Customer Name</th>
	<th>Customer Code</th>
	<th>Product Name</th>
	<th>Product Quantity</th>
	<th>Product Rate</th>
	<th>Net Amount</th>
	<th>Taxation Category</th>
	<th>Tax Amount</th>
	<th>Transport Charges</th>
	<th>Total Amount</th>
	<th>Credit Limit</th>
</tr>

		 <%
        String dte=(String) request.getAttribute("frmdte");
		 String dte1=(String) request.getAttribute("todte");
		 double snet=0.0,stax=0.0,stransport=0.0,stotal=0.0;
		 double v7=0.0,v8=0.0,v9=0.0,v10=0.0,v11=0.0,v12=0.0;
		 try
	        {
	                String qry;
	                Class.forName("com.mysql.jdbc.Driver");
	                Connection con = DriverManager.getConnection("jdbc:mysql://localhost/boa","root","root");
	                Statement s = con.createStatement();                
	                qry=" Select * from salesvoucher where CAST(date as DATE) between '"+dte+"' and '"+dte1+"';";
	                ResultSet r=s.executeQuery(qry);
	                while(r.next()){
        %>
         <% String v1=r.getString(1);
         String v2=r.getString(2);
         String v3=r.getString(3);
         String v4=r.getString(4);
         String v5=r.getString(5);
         int v6=r.getInt(6);
          v7=r.getDouble(7);
          v8=r.getDouble(8);
          v9=r.getDouble(9);
          v10=r.getDouble(10);
          v11=r.getDouble(11);
        v12=r.getDouble(12);
        int v13=r.getInt(13); %>
         <tr>
         <td><%=v1%></td>
         <td><%=v2%></td>
         <td><%=v3%></td>
         <td><%=v4%></td>
         <td><%=v5%></td>
         <td><%=v6%></td>
         <td><%=v7%></td>
         <td><%=v8%></td>
         <td><%=v9%></td>
         <td><%=v10%></td>
         <td><%=v11%></td>
         <td><%=v12%></td>
         <td><%=v13%></td>
         </tr>
         
         <%
         
         snet=snet+v8;
			stax=stax+v10;
			stransport=stransport+v11;
			stotal=stotal+v12;
	                }
	                
        			
        			
        	
        		     
	        }
	        catch(Exception e)
	        {}
		 
	        %>
	        
	        
	        <tr>
	<td> <b> Total </b></td>
	<td> </td>
	<td> </td>
	<td> </td>
	<td> </td>
	<td> </td>
	<td> </td>
	<td> <%=snet %></td>
	<td> </td>
	<td> <%=stax %></td>
	<td> <%=stransport %></td>
	<td> <%=stotal %></td>
	<td> </td>
	
</tr>	


         </table>
         <br><br>
         &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
         <%
        if (exportToExcel == null) {
    %>
    <a href="SalesPeriodicReport.jsp?exportToExcel=YES">Export to Excel</a>
    <%
        }
    %>
</form>


</body>
</html>